EXPLAIN과 EXPLAIN ANALYZE 읽는 순서

목차

• #문제가 되는 상황
• #EXPLAIN과 EXPLAIN ANALYZE의 차이
• #실행 계획은 SQL 문장이 아니라 실행 트리다
• #첫 번째: 가장 안쪽의 데이터 접근부터 읽는다
• #두 번째: 예상 rows와 실제 rows를 비교한다
• #세 번째: loops를 곱해 전체 작업량을 본다
• #네 번째: actual time의 두 숫자를 구분한다
• #다섯 번째: 필터와 정렬에서 버린 행을 찾는다
• #전통적인 EXPLAIN 열을 읽는 순서
• #Nested Loop Join에서 폭증 지점을 찾기
• #LIMIT이 있어도 많이 읽을 수 있다
• #EXPLAIN ANALYZE 실행 시 주의할 점
• #계획을 개선한 뒤 같은 조건에서 비교한다
• #결론
• #관련 노트
• #참고 자료

문제가 되는 상황

느린 목록 쿼리에 EXPLAIN을 실행했더니 기대했던 인덱스 이름이 보였다고 하자.

EXPLAIN
SELECT id, created_at, status
FROM orders
WHERE tenant_id = 42
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

key: ix_orders_tenant_status_created
type: ref
rows: 120
Extra: Using where

인덱스를 사용하고 예상 행도 120개라면 빨라야 할 것 같다. 하지만 실제 실행에는 2초가 걸릴 수 있다. 통계가 현재 데이터 분포와 다르거나, lookup이 반복되거나, 정렬과 조인 뒤에서 행 수가 폭증했을 수 있기 때문이다.

EXPLAIN은 계획을 설명하지만 실제 실행 결과는 아니다. 이때 EXPLAIN ANALYZE로 옵티마이저의 예상과 현실을 비교한다.

EXPLAIN ANALYZE
SELECT id, created_at, status
FROM orders
WHERE tenant_id = 42
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN과 EXPLAIN ANALYZE의 차이

MySQL 8.4 공식 문서 기준으로 EXPLAIN ANALYZE는 문장을 실제 실행하고 TREE 형식의 iterator 정보에 다음 값을 제공한다.

• 예상 비용
• 예상 반환 행 수
• 첫 행을 반환하기까지 걸린 시간
• 마지막 행까지 처리한 시간
• 실제 반환 행 수
• iterator 반복 횟수

이 글의 필드 의미는 MySQL 8.4 EXPLAIN 공식 문서와 EXPLAIN 출력 형식 문서를 기준으로 정리했다.

실행 계획은 SQL 문장이 아니라 실행 트리다

SQL은 원하는 결과를 선언한다. 옵티마이저는 그 결과를 만들기 위한 물리 연산의 순서를 선택한다. 같은 SQL도 데이터 통계와 인덱스에 따라 다른 트리가 될 수 있다.

다음은 이해를 위해 단순화한 가상의 TREE 출력이다.

-> Limit: 20 row(s)
    -> Sort: orders.created_at DESC, limit input to 20 row(s) per chunk
        -> Filter: (orders.status = 'PAID')
            -> Index lookup on orders
               using ix_orders_tenant (tenant_id=42)

들여쓰기가 깊은 아래 노드가 먼저 데이터를 만든다.

flowchart BT
    A[Index lookup: tenant_id=42] --> B[Filter: status=PAID]
    B --> C[Sort: created_at DESC]
    C --> D[Limit: 20]

즉 실행 계획은 아래에서 위로 읽는 편이 자연스럽다.

1. 어떤 테이블 또는 인덱스에서 행을 읽었는가?
2. 어떤 필터로 행을 버렸는가?
3. 조인·집계·정렬에서 몇 행을 처리했는가?
4. 최종 LIMIT과 projection으로 무엇을 반환했는가?

첫 번째: 가장 안쪽의 데이터 접근부터 읽는다

가장 먼저 leaf iterator의 접근 방식을 본다.

-> Table scan on orders

-> Index lookup on orders using ix_orders_tenant (tenant_id=42)

-> Index range scan on orders using ix_orders_tenant_created
   over (tenant_id = 42 AND created_at >= ...)

전체 테이블 스캔이라고 무조건 나쁜 것은 아니다. 테이블 대부분을 반환하는 쿼리는 순차 스캔이 보조 인덱스와 본 테이블을 반복 왕복하는 것보다 저렴할 수 있다. 반대로 결과가 20건인데 수백만 행을 스캔한다면 접근 조건과 인덱스를 점검해야 한다.

다음 질문을 순서대로 적는다.

• 탐색의 시작과 끝을 만드는 조건은 무엇인가?
• 어떤 인덱스를 실제로 선택했는가?
• 복합 인덱스의 어느 접두까지 사용했는가?
• 조건이 index lookup/range인가, 읽은 뒤 filter인가?
• covering access인가, 본 테이블 lookup이 필요한가?

“인덱스를 탔다”보다 “tenant_id로 20만 엔트리를 읽은 뒤 status로 99%를 버렸다”처럼 설명할 수 있어야 한다.

두 번째: 예상 rows와 실제 rows를 비교한다

가상의 출력에 예상과 실제를 붙여 보자.

-> Filter: (orders.status = 'PAID')
   (cost=130 rows=120)
   (actual time=0.080..420 rows=180000 loops=1)

옵티마이저는 120행을 예상했지만 실제로 18만 행이 통과했다. 약 1,500배 차이다. 이런 오차는 뒤 연산의 선택까지 잘못 만들 수 있다.

estimate error ratio = actual rows / estimated rows
                     = 180000 / 120
                     = 1500

큰 오차의 원인은 여러 가지다.

• 통계가 오래됨
• 특정 tenant나 status에 데이터가 몰림
• 두 열 사이의 상관관계를 독립적으로 추정
• 파라미터 값마다 분포가 크게 다름
• 표현식과 함수 결과의 통계 부족
• 조인 열의 중복도 추정 실패

중요한 것은 최상위 노드의 오차만 보는 것이 아니다. 아래에서 위로 올라가며 처음 오차가 크게 발생한 노드를 찾는다. 위쪽의 큰 오차는 아래쪽 추정 실패가 전파된 결과일 수 있다.

세 번째: loops를 곱해 전체 작업량을 본다

MySQL TREE 출력에서 rows와 actual time은 loops가 여러 번이면 반복당 평균으로 표시된다. 따라서 전체 행 처리량을 이해하려면 rows와 loops를 함께 봐야 한다.

-> Index lookup on order_items using ix_items_order (order_id=orders.id)
   (actual time=0.015..0.040 rows=8 loops=50000)

한 번에 8행이라 작아 보이지만 5만 번 반복된다.

total rows produced ≈ rows per loop × loops
                    = 8 × 50,000
                    = 400,000

반복당 0.04ms도 5만 번 누적되면 무시할 수 없다. Nested Loop Join에서는 외부 테이블의 행 수만큼 내부 lookup이 반복되므로 loops가 병목을 드러내는 핵심 값이다.

flowchart LR
    A[외부 orders 50,000행] --> B[각 행마다 order_items lookup]
    B --> C[평균 8행 반환]
    C --> D[총 약 400,000행 처리]

네 번째: actual time의 두 숫자를 구분한다

예시 출력은 다음처럼 두 시간을 보여 준다.

(actual time=2.3..480 rows=200000 loops=1)

개념적으로 첫 숫자는 iterator가 첫 행을 반환하기까지 걸린 시간, 두 번째는 마지막 행까지 반환하는 데 걸린 시간이다. 단위는 밀리초다.

startup/first row time = 2.3ms
completion/last row time = 480ms

둘의 차이는 연산 특성을 보여 준다.

첫 행도 늦은 blocking 연산

Sort
(actual time=480..490 rows=200000 loops=1)

정렬은 입력을 상당 부분 모아야 첫 행을 낼 수 있어 startup time이 클 수 있다. 전체 집계, materialization도 비슷한 성격을 가질 수 있다.

첫 행은 빠르지만 전체 스캔이 긴 연산

Index range scan
(actual time=0.08..480 rows=200000 loops=1)

첫 행은 빨리 나오지만 마지막까지 20만 엔트리를 읽느라 시간이 누적된다. LIMIT이 위에서 일찍 멈출 수 있다면 전체 완료 시간이 줄 수 있지만, 정렬이나 필터 때문에 모든 입력이 필요하면 그렇지 않다.

다섯 번째: 필터와 정렬에서 버린 행을 찾는다

느린 쿼리는 반환 행보다 중간에 읽고 버린 행이 훨씬 많을 때가 많다.

-> Limit: 20 row(s)
    -> Sort: created_at DESC
        -> Filter: (status = 'PAID')
            (actual rows=180000)
            -> Index lookup on orders using ix_tenant
               (actual rows=250000)

이 흐름은 tenant 조건으로 25만 행을 읽고 status 필터로 7만 행을 버린 뒤, 남은 18만 행을 정렬해 20건만 반환한다.

rows read       = 250,000
rows after filter = 180,000
rows sorted     = 180,000
rows returned   = 20

(tenant_id, status, created_at DESC) 인덱스가 쿼리 의미와 맞는다면 PAID 구간의 최신 20개를 읽고 멈추게 할 수 있다.

CREATE INDEX ix_orders_tenant_status_created
ON orders(tenant_id, status, created_at DESC);

그러나 인덱스를 추가하기 전에 이미 비슷한 접두가 있는지, status가 자주 변경되는지, 다른 쿼리의 정렬을 방해하지 않는지 확인한다. 관련 기준은 복합 인덱스의 왼쪽 접두 규칙과 인덱스가 많아질수록 쓰기가 느려지는 이유에서 다뤘다.

전통적인 EXPLAIN 열을 읽는 순서

TABLE 형식 EXPLAIN도 빠른 1차 진단에 유용하다.

MySQL 문서에서 rows는 선택한 접근 방법이 검사할 것으로 추정한 행 수이고, filtered는 테이블 조건을 통과할 것으로 예상한 비율이다. 다음 단계로 전달될 예상 행은 대략 rows × filtered / 100으로 생각할 수 있다.

rows = 100,000
filtered = 5%
next rows ≈ 5,000

possible_keys에 원하는 인덱스가 있지만 key에는 없다고 무조건 옵티마이저가 틀린 것은 아니다. 통계상 전체 스캔이 더 싸다고 판단했을 수 있다. key_len도 바이트 길이만으로 접두 열 사용을 단정하지 말고 ref, 실제 조건과 TREE/JSON 출력을 함께 본다.

접근 type은 보통 ALL보다 range, ref, eq_ref, const가 좁은 접근을 뜻하지만 등급표처럼 외우지 않는다. 작은 테이블의 ALL은 매우 빠를 수 있고, ref가 수백만 행을 반환하면 느릴 수 있다.

Nested Loop Join에서 폭증 지점을 찾기

주문과 주문 항목을 조인하는 예를 보자.

SELECT o.id, i.product_id, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.tenant_id = 42
  AND o.created_at >= '2026-08-01';

가상의 실행 계획:

-> Nested loop inner join
   (actual time=0.1..1800 rows=400000 loops=1)
    -> Index range scan on orders o
       (actual time=0.05..100 rows=50000 loops=1)
    -> Index lookup on order_items i using ix_items_order
       (order_id=o.id)
       (actual time=0.01..0.03 rows=8 loops=50000)

내부 lookup 하나는 빠르지만 5만 번 실행되어 전체 40만 행을 만든다. 병목 후보는 다음과 같다.

• 외부 orders 범위가 너무 넓음
• 필요한 결과 자체가 40만 행이라 전송량이 큼
• order_items lookup 인덱스가 covering이 아니어서 추가 접근 발생
• 조인 후 다시 집계·정렬하여 더 큰 중간 비용 발생

이때 내부 lookup을 0.03ms에서 0.02ms로 줄이는 것보다 외부 행을 5만에서 500으로 줄이는 필터가 더 큰 효과를 낼 수 있다. 실행 트리의 위아래 행 수를 보는 이유다.

LIMIT이 있어도 많이 읽을 수 있다

최종 LIMIT 20만 보고 쿼리가 20행을 읽는다고 생각하면 안 된다.

SELECT id, created_at
FROM orders
WHERE tenant_id = 42
  AND is_featured = true
ORDER BY created_at DESC
LIMIT 20;

인덱스가 (tenant_id, created_at)뿐이고 featured 비율이 0.1%라면 최신순 엔트리 약 2만 개를 읽어야 20건을 얻을 수 있다.

-> Limit: 20
    -> Filter: is_featured = true
        -> Index scan on (tenant_id, created_at)
           actual rows=18,740

또한 ORDER BY가 인덱스와 맞지 않으면 모든 후보를 읽어 정렬한 뒤 LIMIT을 적용할 수 있다. ORDER BY와 LIMIT을 위한 인덱스 설계에서 설명한 조기 종료가 실제 계획에서 나타나는지 actual rows로 확인한다.

EXPLAIN ANALYZE 실행 시 주의할 점

실제로 실행한다

운영에서 수분 걸리는 쿼리를 ANALYZE하면 그 부하가 그대로 발생한다. 먼저 일반 EXPLAIN, 테이블과 인덱스 크기, 예상 범위를 확인한다.

캐시 상태가 결과에 영향을 준다

처음 실행은 스토리지에서 페이지를 읽고 두 번째는 버퍼 풀에서 읽을 수 있다. 차가운 캐시와 따뜻한 캐시를 구분하고 여러 번 측정한다. 운영 버퍼 풀을 임의로 비우는 행동은 피한다.

동시 부하가 시간을 바꾼다

같은 계획도 잠금 대기, I/O 경쟁, CPU 부하에 따라 시간이 달라진다. EXPLAIN ANALYZE는 계획 안의 실행 정보를 주지만 운영의 모든 대기 원인을 설명하지는 않는다. 슬로 쿼리 로그, wait event, 시스템 지표와 함께 본다.

파라미터와 데이터 분포가 중요하다

tenant 1은 10행, tenant 42는 500만 행일 수 있다. 평균값 하나만 분석하지 말고 작은 값, 큰 값, 드문 status와 흔한 status를 각각 실행한다.

민감한 값을 공유하지 않는다

계획을 문서와 이슈에 붙일 때 SQL 리터럴, 테이블명, 고객 식별자에 민감 정보가 없는지 확인한다. 필요하면 구조를 보존한 가상 스키마와 값으로 재구성한다.

계획을 개선한 뒤 같은 조건에서 비교한다

변경 전후를 같은 데이터와 파라미터에서 비교한다.

Before
Index lookup tenant: actual rows=250,000
Filter status:       actual rows=180,000
Sort:                actual rows=180,000
Limit:               20
total:               520ms

After
Index range scan tenant+status+created: actual rows=20
Limit:                                20
total:                                1.8ms

다음 표를 기록하면 재현성이 높아진다.

마지막 INSERT 지표처럼 읽기 개선의 쓰기 비용도 확인한다. 예상과 실제 행 수가 여전히 크게 다르면 통계 갱신, 데이터 상관관계, 인덱스 설계를 다시 검토한다.

결론

EXPLAIN을 읽는 핵심은 선택된 인덱스 이름을 확인하는 것이 아니라 실행 트리를 아래에서 위로 따라가며 데이터가 어디서 많이 생성되고 버려지는지 찾는 것이다. 일반 EXPLAIN은 접근 방법과 예상 행 수를 보여 주고, EXPLAIN ANALYZE는 실제 실행을 통해 first/last row time, rows와 loops를 제공한다.

먼저 가장 안쪽의 scan·lookup에서 시작해 예상 rows와 실제 rows가 처음 크게 어긋나는 지점을 찾는다. 다음으로 rows × loops를 통해 nested loop의 누적 작업량을 보고, 필터·정렬·LIMIT 사이에서 얼마나 많은 행을 읽고 버리는지 확인한다. ANALYZE는 실제 쿼리를 실행하므로 안전한 환경과 대표 데이터 분포에서 사용해야 한다. 개선 후에는 같은 파라미터로 실행 계획과 실제 시간뿐 아니라 쓰기 비용까지 비교해야 비로소 가설을 검증한 것이다.

관련 노트

• MySQL 복합 인덱스의 순서와 중복 인덱스 판단하기
• 인덱스 선택도가 성능에 미치는 영향
• Covering Index로 테이블 접근 줄이기
• ORDER BY와 LIMIT을 위한 인덱스 설계
• 슬로 쿼리 로그에서 개선 후보 찾기
• 인덱스가 많아질수록 쓰기가 느려지는 이유

참고 자료

• MySQL 8.4 Reference Manual: EXPLAIN Statement
• MySQL 8.4 Reference Manual: EXPLAIN Output Format